import json
import os
import threading 
import pymssql 
from django.http import HttpResponse
class TMsSqlConnection:
    def __init__(self) -> None:
        self.code = "cf2022"
        self.host = "dq.hhdata.cn"
        self.port = 41433
        self.db = "C_Fencing"
        self.desc = "击剑数据库"
        self.uid = "sa"
        self.pwd = "Hhuc1115"
        self.sqlPath = ""
        self.connection = None
        self.lock = threading.Lock()
        self.isExecute = False
    def query(self , sql , params):
        res = []
        self.lock.acquire()
        try:
            self.isExecute = True
            if self.connection == None:
                self.connection = pymssql.connect(
                    server=self.host, 
                    port=self.port, 
                    user=self.uid, 
                    password=self.pwd, 
                    database=self.db ,  
                    as_dict =True) 
                self.connection.autocommit(True)
            curror = self.connection.cursor()
            try:
                ps ={}
                if params!=None:
                    try:
                        ps.update(params)
                    except Exception as ee:
                        pass                    
                curror.execute(sql, ps)      
                #curror.executemany(sql, ps) 
                if( (curror.rowcount!=None and curror.rowcount>=0) or (curror.rownumber!=None and curror.rownumber>=0)  ):
                    lastId =0
                    if curror.lastrowid!=None :
                        lastId = int(str(curror.lastrowid))
                    res = {
                        "rowcount": curror.rowcount ,
                        "rownumber": curror.rownumber ,
                        "lastrowid": lastId ,
                    } 
                else:
                    res = curror.fetchall()
            except Exception as err:
                print('---------------------- TMsSqlConnection.query error. ----------------------')
                print(sql , params)
                print(err)
                print('----------------------------------------------')
            curror.close()
        except Exception as er :
            print('---------------------- TMsSqlConnection.query error. ----------------------')
            print(sql , params)
            print(er)
            print('----------------------------------------------')
        self.isExecute = False
        self.lock.release()
        return res
    def queryBySqlPath(self , sqlPath , params):
        res = []
        try:
            sql = self.getSql(sqlPath)
            if sql == "":
                print('---------------------- TMsSqlConnection.queryBySqlPath sqlPath is error. ----------------------')
                print(sqlPath ) 
            else:
                res = self.query(sql , params)
        except Exception as er :
            print('---------------------- TMsSqlConnection.queryBySqlPath error. ----------------------')
            print(sqlPath , params)
            print(er)
            print('----------------------------------------------')
        return res
    def getSql(self , sqlPath ):
        res = ""
        try:
            sqlFn = os.path.join( self.sqlPath , sqlPath)
            if sqlFn.find('.txt')<0 :
                sqlFn = sqlFn +".txt"
            flag = os.path.exists( sqlFn)
            if flag:
                with open(sqlFn , 'r', encoding='utf-8-sig') as f:
                    res = f.read()
        except Exception as er :
            print('---------------------- TMsSqlConnection.getSql error. ----------------------')
            print(sqlPath )
            print(er)
            print('----------------------------------------------')
        return res
class TMsSql:
    def __init__(self) -> None:
        self.connections ={}
    def setConnection ( self  , code ,  host , port , db , uid , pwd , sqlPath):
        try:
            conn = None
            if code in self.connections:
                conn = self.connectionsp[code]
                if conn.connection!=None and conn.connection.isConnected():
                    conn.connection.close()
                    conn.connection = None
            else:
                conn = TMsSqlConnection()
                conn.code = code
                self.connections[code] = conn
            conn.host = host
            conn.port = port
            conn.db = db
            conn.uid = uid
            conn.pwd = pwd
            conn.sqlPath = sqlPath
        except Exception as er:
            print('---------------------- TMsSql.setConnection error. ----------------------') 
            print(er)
            print('----------------------------------------------')
    def query ( self  , code ,  sqlPath , params):
        res =[]
        try:
            connection = None
            if code in self.connections:
                connection = self.connections[code]
                res = connection.queryBySqlPath(sqlPath , params)
            else:
                print('---------------------- TMsSql.query code is error. ----------------------' , code) 

        except Exception as er:
            print('---------------------- TMsSql.query error. ----------------------') 
            print(er)
            print('----------------------------------------------')
        return res
